<?php
namespace Dry\Base;

class Sql {

    private $field = '*';

    private $table = '';

    private $insertOrUpdate = array();

    private $where = array();

    private $order = array();

    private $limit = '';

    private $placeholder = array();

    private $total = array();

    //恢复默认设置
    private function reset()
    {
        $this->field = '*';
        $this->table = '';
        $this->insertOrUpdate = array();
        $this->where = array();
        $this->order = array();
        $this->limit = '';
        $this->placeholder = array();
    }

    //设置字段
    public function field($field)
    {
        $this->field = $field;
        return $this;
    }

    //设置表
    public function table($table)
    {
        $this->table = $table;
        return $this;
    }

    public function set($field, $value)
    {
        $placeholder = $this->getUniqid($field);
        $this->placeholder[$placeholder] = $value;
        $this->insertOrUpdate['field'][] = $field;
        $this->insertOrUpdate['value'][] = $placeholder;
        return $this;
    }

    private function getUniqid($field)
    {
        $result = uniqid(":{$field}_", true);
        $result = str_replace('.', '', $result);
        return $result;
    }

    //设置查询条件
    public function where($field, $operator, $value)
    {
        if(in_array($operator, array('<', '<=', '=', '>=', '>', '<>'))){
            $placeholder = $this->getUniqid($field);
            $this->placeholder[$placeholder] = $value;
            $this->where[] = "{$field} {$operator} {$placeholder}";
        }
        elseif(in_array($operator, array('like', 'not like'))){
            $placeholder = $this->getUniqid($field);
            $this->placeholder[$placeholder] = $value;
            $this->where[] = "{$field} {$operator} '%{$placeholder}%'";
        }
        elseif($operator=='between and'){
            $placeholder0 = $this->getUniqid($field);
            $placeholder1 = $this->getUniqid($field);
            $this->placeholder[$placeholder0] = $value[0];
            $this->placeholder[$placeholder1] = $value[1];
            $this->where[] = "{$field} between {$placeholder0} and {$placeholder1}";
        }
        elseif($operator=='contain'){
            $placeholder = $this->getUniqid($field);
            $this->placeholder[$placeholder] = $value;
            $this->where[] = "FIND_IN_SET({$placeholder},{$field})>0";
        }
        elseif($operator=='not contain'){
            $placeholder = $this->getUniqid($field);
            $this->placeholder[$placeholder] = $value;
            $this->where[] = "FIND_IN_SET({$placeholder},{$field})<=0";
        }
        elseif($operator=='match word'){
            $placeholder = $this->getUniqid($field);
            $this->placeholder[$placeholder] = $value;
            $this->where[] = "{$field} REGEXP '[[:<:]]{$placeholder}[[:>:]]'";
        }
        elseif(in_array($operator, array('in', 'not in'))){
            $placeholder = $this->getUniqid($field);
            $this->placeholder[$placeholder] = $value;
            $this->where[] = "{$field} {$operator} ({$placeholder})";
        }
        return $this;
    }

    //设置查询条件and
    public function andWhere($field, $operator, $value)
    {
        $this->whereExpression('and');
        $this->where($field, $operator, $value);
        return $this;
    }

    //设置查询条件or
    public function orWhere($field, $operator, $value)
    {
        $this->whereExpression('or');
        $this->where($field, $operator, $value);
        return $this;
    }

    //设置筛选连接符
    public function whereExpression($operator)
    {
        switch($operator){
            case '(':
                $this->where[] = '(';
                break;
            case ')':
                $this->where[] = ')';
                break;
            case 'and':
                $this->where[] = ' and ';
                break;
            case 'or':
                $this->where[] = ' or ';
            break;
        }
        return $this;
    }

    //设置排序
    public function order($field, $method)
    {
        $this->order[] = "{$field} {$method}";
        return $this;
    }

    //设置限制数
    public function limit($page, $pagesize)
    {
        $start = ($page-1)*$pagesize;
        $this->limit = "limit {$start},{$pagesize}";
        return $this;
    }

    //返回sql语句
    public function get()
    {
        $where = '';
        $order = '';
        if(!empty($this->where)){
            $where = 'where '.implode('', $this->where);
        }
        if(!empty($this->order)){
            $order = 'order by '.implode(',', $this->order);
        }
        $result = array(
            'sql' => "select {$this->field} from {$this->table} {$where} {$order} {$this->limit}",
            'placeholder' => $this->placeholder
        );
        $this->total = array(
            'sql' => "select count(1) as total from {$this->table} {$where}",
            'placeholder' => $this->placeholder
        );
        $this->reset();
        return $result;
    }

    //返回sql语句
    public function getTotalSql()
    {
        return $this->total;
    }

    //返回sql语句
    public function getInsertSql()
    {
        $field = implode(',', $this->insertOrUpdate['field']);
        $value = implode(',', $this->insertOrUpdate['value']);
        $result = array(
            'sql' => "insert into {$this->table}({$field}) values({$value})",
            'placeholder' => $this->placeholder
        );
        $this->reset();
        return $result;
    }

    //返回sql语句
    public function getUpdateSql()
    {
        $where = '';
        if(!empty($this->where)){
            $where = 'where '.implode('', $this->where);
        }
        $update = array();
        foreach($this->insertOrUpdate['field'] as $k => $field){
            $update[] = "{$field}={$this->insertOrUpdate['value'][$k]}";
        }
        $update = implode(',', $update);
        $result = array(
            'sql' => "update {$this->table} set {$update} {$where}",
            'placeholder' => $this->placeholder
        );
        $this->reset();
        return $result;
    }

    //返回sql语句
    public function getDeleteSql()
    {
        $where = '';
        if(!empty($this->where)){
            $where = 'where '.implode('', $this->where);
        }
        $result = array(
            'sql' => "delete from {$this->table} {$where}",
            'placeholder' => $this->placeholder
        );
        $this->reset();
        return $result;
    }

}
?>
